--------------------------------------------------------------------------------
-- @name: row migration and chaining
-- @author: dion cho
-- @note: test case for the row migration and chaining
--------------------------------------------------------------------------------

@capture_on
set echo on
set pages 10000
set lines 200
set timing off
set trimspool on

drop table t_mig purge;

create table t_mig(
	c1 number, 
	c2 varchar2(2000), 
	c3 varchar2(1000)
);

--------------------------------------------------------------
-- case1: row migration
-- insert non migrated rows
insert into t_mig
select 
	level, 
	decode(mod(level,3), 1, null, rpad('x',2000,'x')),
	decode(mod(level,3), 1, null, rpad('x',1000,'x'))
from dual
connect by level <= 1000;

-- chain_cnt
analyze table t_mig compute statistics;

select num_rows, blocks, chain_cnt
from user_tables
where table_name = 'T_MIG';

-- get the 1st block(c1 = 1)
col fno new_value fno
col bno new_value bno
select 
	dbms_rowid.rowid_relative_fno(rowid) as fno,
	dbms_rowid.rowid_block_number(rowid) as bno
from t_mig
where c1 = 1;

select * from table(tpack.block_dump(&fno, &bno, 0));

-- make 1/3 of the rows migrated
update t_mig set
	c2 = rpad('x',2000,'x'),
	c3 = rpad('x',1000,'x')
where
	mod(c1, 3) = 1
;

analyze table t_mig compute statistics;

select num_rows, blocks, chain_cnt
from user_tables
where table_name = 'T_MIG';

select * from table(tpack.block_dump(&fno, &bno, 0));

col nrid new_value nrid
select tpack.to_dec(regexp_replace(column_value, 
		'nrid:[[:space:]]+0x([[:xdigit:]]+).0', '\1')) as nrid
from table(tpack.block_dump(&fno, &bno, 0))
where column_value like 'nrid: %' and rownum = 1;

col fno new_value fno
col bno new_value bno
select 
	dbms_utility.data_block_address_file(&nrid) as fno,
	dbms_utility.data_block_address_block(&nrid) as bno
from dual;

select * from table(tpack.block_dump(&fno, &bno, 0));

--@diff result1.txt result2.txt


-- table fetch continued row
exec tpack.begin_session_snapshot;

-- for table full scan
select /*+ full(t_mig) */ count(c2) from t_mig;

exec tpack.add_session_snapshot;

col type format a4
col item format a30
col deltas format a10
select * from table(tpack.session_snapshot_report) 
where type = 'STAT' and item like 'table fetch continued row';

-- for index lookup
create index t_mig_n1 on t_mig(c1);

exec tpack.begin_session_snapshot;

select /*+ index(t_mig) */ 
	count(c2) 
from t_mig
where c1 > 0;

exec tpack.add_session_snapshot;

col type format a4
col item format a30
col deltas format a10
select * from table(tpack.session_snapshot_report) 
where type = 'STAT' and item like 'table fetch continued row';


-- after table move
alter table t_mig move;

alter index t_mig_n1 rebuild;

exec tpack.begin_session_snapshot;

select /*+ index(t_mig) */ 
	count(c2) 
from t_mig
where c1 > 0;

exec tpack.add_session_snapshot;

col type format a4
col item format a30
col deltas format a10
select * from table(tpack.session_snapshot_report) 
where type = 'STAT' and item like 'table fetch continued row';


--------------------------------------------------------------
-- case2 : row chaining
drop table t_chn purge;

create table t_chn(
	c1 number,
	c2 varchar2(2000),
	c3 varchar2(2000),
	c4 varchar2(2000),
	c5 varchar2(2000),
	c6 varchar2(2000),
	c7 varchar2(2000),
	c8 varchar2(2000),
	c9 varchar2(2000),
	c10 varchar2(2000),
	c11 varchar2(2000)
);

-- insert chinaged row
insert into t_chn
select
	level,
	rpad('x',2000,'x'),
	rpad('x',2000,'x'),
	rpad('x',2000,'x'),
	rpad('x',2000,'x'),
	rpad('x',2000,'x'),
	rpad('x',2000,'x'),
	rpad('x',2000,'x'),
	rpad('x',2000,'x'),
	rpad('x',2000,'x'),
	rpad('x',2000,'x')
from dual
connect by level <= 1000;


-- chain_cnt
analyze table t_chn compute statistics;

select num_rows, blocks, chain_cnt
from user_tables
where table_name = 'T_CHN';

-- block dump
-- get the 1st block(c1 = 1)
col fno new_value fno
col bno new_value bno
select 
	dbms_rowid.rowid_relative_fno(rowid) as fno,
	dbms_rowid.rowid_block_number(rowid) as bno
from t_chn
where c1 = 1;

select * from table(tpack.block_dump(&fno, &bno, 1));

-- get next block
col nrid new_value nrid
select tpack.to_dec(regexp_replace(column_value, 
		'nrid:[[:space:]]+0x([[:xdigit:]]+).0', '\1')) as nrid
from table(tpack.block_dump(&fno, &bno, 0))
where column_value like 'nrid: %' and rownum = 1;

col fno new_value fno
col bno new_value bno
select 
	dbms_utility.data_block_address_file(&nrid) as fno,
	dbms_utility.data_block_address_block(&nrid) as bno
from dual;

select * from table(tpack.block_dump(&fno, &bno, 0));

--The flag byte shows that this row piece is the head row piece (H), 
-- the first row piece (F) and the last row piece (L) of its row


-- table fetch continued row

-- for table full scan
exec tpack.begin_session_snapshot;

select /*+ full(t_chn) */ count(c5) from t_chn;

exec tpack.add_session_snapshot;

col type format a4
col item format a30
col deltas format a10
select * from table(tpack.session_snapshot_report)
where type = 'STAT' and item like 'table fetch continued row'
;

-- for index lookup
create index t_chn_n1 on t_chn(c1);

exec tpack.begin_session_snapshot;

select /*+ index(t_chn) */ 
	count(c5) 
from t_chn
where c1 > 0;

exec tpack.add_session_snapshot;

col type format a4
col item format a30
col deltas format a10
select * from table(tpack.session_snapshot_report) 
where type = 'STAT' and item like 'table fetch continued row';


-- after table move
alter table t_chn move;

-- table fetch continued row

-- for table full scan
exec tpack.begin_session_snapshot;

select /*+ full(t_chn) */ count(c5) from t_chn;

exec tpack.add_session_snapshot;

col type format a4
col item format a30
col deltas format a10
select * from table(tpack.session_snapshot_report)
where type = 'STAT' and item like 'table fetch continued row'
;

-- for index lookup
alter index t_chn_n1 rebuild;

exec tpack.begin_session_snapshot;

select /*+ index(t_chn) */ 
	count(c5) 
from t_chn
where c1 > 0;

exec tpack.add_session_snapshot;

col type format a4
col item format a30
col deltas format a10
select * from table(tpack.session_snapshot_report) 
where type = 'STAT' and item like 'table fetch continued row';



--------------------------------------------------------------
-- case3 : intra-block row chaining
drop table t_chn2 purge;

declare
	v_sql		varchar2(20000);
begin
	v_sql := 'create table t_chn2 ('  || chr(10);
	
	for idx in 1 .. 299 loop
		v_sql := v_sql || 'c' || idx || ' number default 1, ' || chr(10);
	end loop;
	
	v_sql := v_sql || 'c300 number default 1)';

	execute immediate v_sql;
end;
/


insert into t_chn2(c1)
select 
	level
from dual connect by level <= 1000;


-- chain_cnt
analyze table t_chn2 compute statistics;

select num_rows, blocks, chain_cnt
from user_tables
where table_name = 'T_CHN2';

-- get the 1st block(c1 = 1)
col fno new_value fno
col bno new_value bno
select 
	dbms_rowid.rowid_relative_fno(rowid) as fno,
	dbms_rowid.rowid_block_number(rowid) as bno
from t_chn2
where c1 = 1;

select * from table(tpack.block_dump(&fno, &bno, 0));

-- table fetch continued row

-- for table full scan(45th column)
exec tpack.begin_session_snapshot;

select /*+ full(t_chn2) */ count(c45) from t_chn2;

exec tpack.add_session_snapshot;

col type format a4
col item format a30
col deltas format a10
select * from table(tpack.session_snapshot_report)
where type = 'STAT' and item like 'table fetch continued row'
;


-- for table full scan (46th column)
exec tpack.begin_session_snapshot;

select /*+ full(t_chn2) */ count(c46) from t_chn2;

exec tpack.add_session_snapshot;

col type format a5
col item format a30
col deltas format a10
select * from table(tpack.session_snapshot_report)
where type = 'STAT' and item like 'table fetch continued row'
;


-- after table move
alter table t_chn2 move;

-- get the 1st block(c1 = 1)
col fno new_value fno
col bno new_value bno
select 
	dbms_rowid.rowid_relative_fno(rowid) as fno,
	dbms_rowid.rowid_block_number(rowid) as bno
from t_chn2
where c1 = 1;

select * from table(tpack.block_dump(&fno, &bno, 0));


-- 255th column
exec tpack.begin_session_snapshot;

select /*+ full(t_chn2) */ count(c255) from t_chn2;

exec tpack.add_session_snapshot;

col type format a5
col item format a30
col deltas format a10
select * from table(tpack.session_snapshot_report)
where type = 'STAT' and item like 'table fetch continued row'
;

-- 256th column
exec tpack.begin_session_snapshot;

select /*+ full(t_chn2) */ count(c256) from t_chn2;

exec tpack.add_session_snapshot;

col type format a5
col item format a30
col deltas format a10
select * from table(tpack.session_snapshot_report)
where type = 'STAT' and item like 'table fetch continued row'
;



@capture_off
